package cn.pconline.officialclub.service;

import cn.pconline.autoclub.util.Pager;
import cn.pconline.officialclub.entity.OClub;
import cn.pconline.officialclub.entity.OClubContact;
import cn.pconline.officialclub.entity.OClubFocusImg;
import cn.pconline.officialclub.entity.OClubNotice;
import cn.pconline.officialclub.entity.OCommonMember;
import cn.pconline.officialclub.entity.OForum;
import com.danga.MemCached.MemCachedClient;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.gelivable.dao.GeliOrm;
import org.gelivable.dao.SqlBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;

/**
 * 官方车友会相关
 *
 * @author <a href="mailto:chensy0203@163.com">chensy</a>
 */
public class OClubService extends ServiceKit {

    @Autowired
    private GeliOrm geliOrm;
    @Autowired
    private MemCachedClient mcc;

    /**
     * 查找状态正常(已经发布)的品牌车友会
     *
     * @param oClubId
     * @return
     */
    public OClub findValidClub(long oClubId) {
        if (oClubId < 1) {
            return null;
        }
        OClub club = findById(OClub.class, oClubId);
        if (club != null && club.getStatus() == OClub.STATUS_NORMAL) {
            return club;
        }
        return null;
    }

    public int updateClub(OClub oclub) {
        oclub.setUpdateAt(new Date());
        return geliDao.update(oclub);
    }

    public int updateLogo(OClub oclub) {
        oclub.setUpdateAt(new Date());
        return geliDao.update(oclub, "update_at,update_by,logo");
    }

    public int updateCoverImage(OClub oclub) {
        oclub.setUpdateAt(new Date());
        return geliDao.update(oclub, "update_at,update_by,cover_image");
    }

    public int updateIntroduce(OClub oclub) {
        oclub.setUpdateAt(new Date());
        return geliDao.update(oclub, "update_at,update_by,introduce");
    }

    /**
     * 更新商家信息
     *
     * @param contact
     * @return
     */
    public int updateClubContact(OClubContact contact) {
        contact.setUpdateAt(new Date());
        return geliDao.update(contact);
    }

    /**
     * 更新公告信息
     *
     * @param oClubNotice
     * @return
     */
    public int updateClubNotice(OClubNotice oClubNotice) {
        oClubNotice.setUpdateAt(new Date());
        return geliDao.update(oClubNotice);
    }

    /**
     * 更新会员数
     *
     * @param oClubNotice
     * @return
     */
    public int updateMemberNum(long oClubId, int memberNum) {
        OClub club = findById(OClub.class, oClubId);
        club.setMemberNum(memberNum);
        return geliDao.update(club, "member_num");
    }

    /**
     * 更新会员数，VIP数
     *
     * @param oClubNotice
     * @return
     */
    public int updateCommonNum(long oClubId, int memberNum, int vipNum) {
        OClub club = findById(OClub.class, oClubId);
        club.setMemberNum(memberNum);
        club.setVipNum(vipNum);
        return geliDao.update(club, "member_num,vip_num");
    }

    /**
     * 更新VIP数
     *
     * @param oClubNotice
     * @return
     */
    public int updateVipNum(long oClubId, int vipNum) {
        OClub club = findById(OClub.class, oClubId);
        club.setVipNum(vipNum);
        return geliDao.update(club, "vip_num");
    }

    /**
     * 更新入驻车友会个数
     *
     * @param oClubNotice
     * @return
     */
    public int updateClubNum(long oClubId, int clubNum) {
        OClub club = findById(OClub.class, oClubId);
        club.setClubNum(clubNum);
        return geliDao.update(club, "club_num");
    }

    /**
     * 根据品牌车友会ID查询商家信息
     *
     * @param oClubId
     * @return
     */
    public OClubContact findClubContact(long oClubId) {
        return findById(OClubContact.class, oClubId);
    }

    public long createOForum(OForum oForum) {
        if (oForum == null) {
            return 0;
        }
        return geliDao.create(oForum);
    }

    /**
     * 物理删除官方车友会
     *
     * @param oClub
     * @return
     */
    public int deleteClub(OClub oClub) {
        return geliDao.delete(oClub, oClub.getoClubId());
    }

    /**
     * 删除官方车友会联系人
     *
     * @param oClubContact
     * @return
     */
    public int deleteClubContact(OClubContact oClubContact) {
        return geliDao.delete(oClubContact, oClubContact.getOClubId());
    }

    /**
     * 组合查询车友会
     *
     * @param oClubId 车友会ID
     * @param clubUrl 车友会域名
     * @param name 名称
     * @param area 地区
     * @param brandName 品牌名称
     * @param seriesName 车系名称
     * @param provinceId 省份ID
     * @param regionId 地区ID
     * @param levelId 级别ID
     * @param beginDate 创建开始时间
     * @param endDate 创建结束时间
     * @param status 状态
     * @param orderField 排序字段
     * @param orderDirection 升降序
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<OClub> pageClubBy(
            long oClubId,
            String clubUrl,
            String name,
            String area,
            String brandName,
            String seriesName,
            long regionId,
            Date beginDate,
            Date endDate,
            int tagId,
            int status,
            String orderField,
            String orderDirection,
            int pageNo,
            int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select * from ").appendSql(getTableName(OClub.class)).appendSql(" where 1=1");
        if (oClubId > 0) {
            sql.appendSql(" and o_club_id=").appendValue(oClubId);
        }
        if (StringUtils.isNotBlank(clubUrl)) {
            sql.appendSql(" and url=").appendValue(clubUrl);
        }
        if (StringUtils.isNotBlank(name)) {
            sql.appendSql(" and name like ").appendValue("%" + name + "%");
        }
        if (StringUtils.isNotBlank(area)) {
            sql.appendSql(" and (province_name like ").appendValue("%" + area + "%");
            sql.appendSql(" or city_name like ").appendValue("%" + area + "%").appendSql(")");
        }
        if (StringUtils.isNotBlank(brandName)) {
            sql.appendSql(" and brand_name like ").appendValue("%" + brandName + "%");
        }
        if (StringUtils.isNotBlank(seriesName)) {
            sql.appendSql(" and series_name like ").appendValue("%" + seriesName + "%");
        }
        if (tagId > OClub.STATUS_ALL) {
            sql.appendSql(" and tag_id = ").appendValue(tagId);
        }
        if (regionId > 0) {
            if (regionId == 8) {
                //港澳台--目前不支持台湾
                sql.appendSql(" and province_id in (810000,820000,710000)");
            } else {
                sql.appendSql(" and region_id =").appendValue(regionId);
                sql.appendSql(" and province_id not in (810000,820000,710000)");
            }
        }
        if (beginDate != null) {
            sql.appendSql(" and create_at >=").appendValue(beginDate);
        }
        if (endDate != null) {
            sql.appendSql(" and create_at <=").appendValue(endDate);
        }
        if (status > OClub.STATUS_ALL) {
            sql.appendSql(" and status=").appendValue(status);
        }
        if (StringUtils.isNotBlank(orderField)) {
            sql.appendSql(" order by ").appendSql(geliOrm.getColumnByField(OClub.class, orderField)).appendSql(" ").appendSql(orderDirection);
        } else {
            sql.appendSql(" order by create_at desc");
        }
        return pagerService.pager(OClub.class, sql, pageNo, pageSize);
    }

    /**
     * 创建品牌车友会实体
     *
     * @param oClub
     * @return
     */
    public long createClub(OClub oClub) {
        oClub.setCreateAt(new Date());
        oClub.setUpdateAt(new Date());
        oClub.setVipNum(0);
        return geliDao.create(oClub);
    }

    /**
     * 创建商家信息
     *
     * @param oClubContact
     * @return
     */
    public long createClubContact(OClubContact oClubContact) {
        oClubContact.setCreateAt(new Date());
        oClubContact.setUpdateAt(new Date());
        return geliDao.create(oClubContact);
    }

    /**
     * 根据域名查询车友会
     *
     * @param url
     * @return
     */
    public long findClubIdByUrl(String url) {
        if (StringUtils.isBlank(url)) {
            return 0;
        }
        String key = "OClubUrl-" + url;
        Long id = (Long) mcc.get(key);
        if (id != null && id > 0) {
            return id;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select o_club_id from ").appendSql(geliOrm.getTableName(OClub.class));
        sql.appendSql(" where url = ").appendValue(url);
        try {
            id = (long)jdbcTemplate.queryForInt(sql.getSql(), sql.getValues());
            mcc.add(key, id);
            return id;
        } catch (EmptyResultDataAccessException e) {
            return 0;
        }
    }

    /**
     * 是否存在名称
     *
     * @param name
     * @return
     */
    public boolean isNameExist(String name) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select count(1) from ").appendSql(getTableName(OClub.class));
        sql.appendSql(" where name=").appendValue(name);
        return count(sql) > 0;
    }

    /**
     * 是否存在域名
     *
     * @param url
     * @return
     */
    public boolean isUrlExist(String url) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select count(1) from ").appendSql(getTableName(OClub.class));
        sql.appendSql(" where url=").appendValue(url);
        return count(sql) > 0;
    }

    /**
     * 查找车友会公告,没有就创建
     *
     * @param oClubId
     * @return
     */
    public OClubNotice findClubNotice(long oClubId) {
        OClubNotice oClubNotice = findById(OClubNotice.class, oClubId);
        if (oClubNotice == null) {
            oClubNotice = new OClubNotice();
            oClubNotice.setoClubId(oClubId);
            oClubNotice.setContent("");
            oClubNotice.setStatus(OClubNotice.STATUS_NOT_AUDITED);
            oClubNotice.setUpdateAt(new Date());
            geliDao.create(oClubNotice);
        }
        return oClubNotice;
    }

    public OForum findForum(long fid) {
        return findById(OForum.class, fid);
    }

    /**
     * 保存焦点图
     *
     * @param list
     * @return
     */
    public int saveFocusImg(List<OClubFocusImg> list) {
        int count = 0;
        if (list != null && !list.isEmpty()) {
            for (OClubFocusImg oClubFocusImg : list) {
                String imgUrl = oClubFocusImg.getImgUrl();
                if (StringUtils.isBlank(imgUrl)) { // 删除焦点图
                    oClubFocusImg.setTitle("");
                    oClubFocusImg.setSeq(OClubFocusImg.EMPTY_SEQ);
                    oClubFocusImg.setUrl("");
                }
                if (oClubFocusImg.getFocusImgId() > 0) {
                    if (geliDao.update(oClubFocusImg) > 0) {
                        count++;
                    }
                } else {
                    if (geliDao.create(oClubFocusImg) > 0) {
                        count++;
                    }
                }
            }
        }
        return count;
    }

    /**
     * 逻辑删除指定焦点图
     *
     * @param focusId
     * @return
     */
    public boolean deleteFocusImgById(long focusId) {
        boolean result = false;
        OClubFocusImg focusImg = findById(OClubFocusImg.class, focusId);
        focusImg.setImgUrl("");
        focusImg.setTitle("");
        focusImg.setUrl("");
        focusImg.setSeq(OClubFocusImg.EMPTY_SEQ);
        if (geliDao.update(focusImg) > 0) {
            result = true;
        }
        return result;
    }

    /**
     * 最多只拿三张图片
     *
     * @param oClubId
     * @return
     */
    public List<OClubFocusImg> listFocusImg(long oClubId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select focus_img_id from ").appendSql(getTableName(OClubFocusImg.class));
        sql.appendSql(" where o_club_id = ").appendValue(oClubId).appendSql(" order by seq ");
        return listLimit(OClubFocusImg.class, sql, 3);
    }

    /**
     * 查询用户加入的品牌车友会
     *
     * @param userId
     * @return
     */
    public List<OClub> listClub4Join(long userId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select m.o_club_id from ").appendSql(getTableName(OCommonMember.class)).appendSql(" m ,").appendSql(getTableName(OClub.class)).appendSql(" o ");
        sql.appendSql(" where m.o_club_id = o.o_club_id and m.user_id=").appendValue(userId).appendSql(" and o.status=").appendValue(OClub.STATUS_NORMAL);
        return list(OClub.class, sql);
    }

    /**
     * 查询加入的品牌车友会的个数
     *
     * @param oClubId
     * @return
     */
    public int countClub4Join(long userId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select count(1) from ").appendSql(getTableName(OCommonMember.class));
        sql.appendSql(" where user_id = ").appendValue(userId);
        return count(sql);
    }

    /**
     * 分页查询用户加入的品牌车友会
     *
     * @param uid
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<OClub> pagerClubJoin(long uid, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select c.o_club_id from ").appendSql(getTableName(OCommonMember.class)).appendSql(" a left join ").appendSql(getTableName(OClub.class)).appendSql(" c on a.o_club_id = c.o_club_id ").appendSql(" where c.status=").appendValue(OClub.STATUS_NORMAL).appendSql(" and a.user_id=").appendValue(uid);
        sql.appendSql(" order by a.join_at asc");
        return pagerService.pager(OClub.class, sql, pageNo, pageSize);
    }

    /**
     * 根据用户ID和版块ID查询用户加入的车友会
     *
     * @param userId
     * @param forumId
     * @return
     */
    public List<OClub> queryClubBy(long userId, long forumId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select c.o_club_id from ").appendSql(getTableName(OClub.class)).appendSql(" c,").appendSql(getTableName(OCommonMember.class)).appendSql(" m where c.o_club_id = m.o_club_id").appendSql(" and c.status=").appendValue(OClub.STATUS_NORMAL);
        if (forumId > 0) {
            sql.appendSql(" and c.forum_id=").appendValue(forumId);
        }
        sql.appendSql(" and m.user_id=").appendValue(userId);
        return list(OClub.class, sql);
    }

    /**
     * 根据版块ID查询品牌车友会(只查第一个)
     *
     * @param forumId
     * @return
     */
    public OClub queryClubByForumId(long forumId) {
        if (forumId == 0) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select * from ").appendSql(getTableName(OClub.class)).appendSql(" where forum_id=").appendValue(forumId).appendSql(" and status=").appendValue(OClub.STATUS_NORMAL);
        sql.appendSql(" order by create_at desc");
        return findFirst(OClub.class, sql);
    }

    /**
     * 根据车系ID查询品牌车友会
     *
     * @param seriesId
     * @return
     */
    public OClub queryClubBySeriesId(long seriesId) {
        if (seriesId == 0) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select * from ").appendSql(getTableName(OClub.class)).appendSql(" where series_id=").appendValue(seriesId).appendSql(" and status=").appendValue(OClub.STATUS_NORMAL);
        return findFirst(OClub.class, sql);
    }
    
    /**
     * 根据城市ID查询品牌车友会
     *
     * @param cityId
     * @return
     */
    public OClub findClubByCityId(long cityId) {
        if (cityId == 0) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select * from ").appendSql(getTableName(OClub.class)).appendSql(" where city_id=").appendValue(cityId).appendSql(" and status=").appendValue(OClub.STATUS_NORMAL);
        return findFirst(OClub.class, sql);
    }
    
    /**
     * 根据省份ID查询品牌车友会
     *
     * @param provinceId
     * @return
     */
    public OClub findClubByProvinceId(long provinceId) {
        if (provinceId == 0) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select * from ").appendSql(getTableName(OClub.class)).appendSql(" where province_id=").appendValue(provinceId).appendSql(" and status=").appendValue(OClub.STATUS_NORMAL);
        return findFirst(OClub.class, sql);
    }

    /**
     * 按标签列出会员数排行前num的车友会
     *
     * @param seriesId
     * @return
     */
    public List<OClub> listClubByTag(long tagId, int num) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select o_club_id from ").appendSql(getTableName(OClub.class));
        sql.appendSql(" where tag_id=").appendValue(tagId).appendSql(" order by member_num desc,name asc");
        return listLimit(OClub.class, sql, num);
    }

    /**
     * 按标签分页列出会员数排行前num的车友会
     *
     *
     */
    public Pager<OClub> pagerClubByTag(long tagId, int pageNo, int pageSize, int status) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select o_club_id from ").appendSql(getTableName(OClub.class));
        sql.appendSql(" where tag_id=").appendValue(tagId);
        sql.appendSql(" and status = ").appendValue(status).appendSql(" order by member_num desc,name asc");
        return pagerService.pager(OClub.class, sql, pageNo, pageSize);
    }

    /**
     * 根据ID列出已经是发布状态的车友会
     *
     * @param seriesId
     * @return
     */
    public List<OClub> listClubByIds(Object... ids) {
        if (ids == null || ids.length == 0) {
            return Collections.emptyList();
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select o_club_id from ").appendSql(getTableName(OClub.class));
        sql.appendSql(" where status = ").appendValue(OClub.STATUS_NORMAL).appendSql(" and o_club_id in ").appendValues(ids);
        return list(OClub.class, sql);
    }
}
